//获取租赁列表
const select_lease = "select smart_household_owner.user_id,building_name,house_plate,house_area,lease_title,lease_price,lease_imageUrl,lease_introduce,lease_contacts,lease_tel,lease_keyword,lease_time,lease_index from smart_lease left join smart_house on smart_lease.house_id=smart_house.id left join smart_building on smart_house.building_id=smart_building.id left join smart_household_owner on smart_house.owner_id=smart_household_owner.id where lease_status=1 limit ? offset ?";

//获取租赁列表(价格区间筛选)
const select_lease_between = "select smart_household_owner.user_id,building_name,house_plate,house_area,lease_title,lease_price,lease_imageUrl,lease_introduce,lease_contacts,lease_tel,lease_keyword,lease_time,lease_index from smart_lease left join smart_house on smart_lease.house_id=smart_house.id left join smart_building on smart_house.building_id=smart_building.id left join smart_household_owner on smart_house.owner_id=smart_household_owner.id where lease_status=1 and lease_price between ? and ? limit ? offset ?";

//获取栋数信息
const select_buildings = "select id,building_name from smart_building";

// 获取住户信息
const select_house = "select id,house_plate from smart_house where building_id=?";

//获取所有聊天记录
const select_chat_all = "select nickName,avatarUrl,chat_sendContent,chat_receiver_status,chat_sendTime from smart_chat left join smart_user on receiver_id=smart_user.id where sender_id=? and receiver_id=? order by chat_sendTime desc";

//记录聊天记录
const insert_chat = "insert into smart_chat(sender_id,receiver_id,chat_sendContent,chat_sendTime,chat_receiver_status,chat_sender_status,chat_receiver_reading) values (?,?,?,?,1,1,0)";

//获取未读消息
const isnotReading_chat = "select sender_id,nickName,avatarUrl,chat_sendContent,chat_sendTime from smart_chat left join smart_user on receiver_id=smart_user.id where receiver_id=? and chat_receiver_reading=0";

//改变消息为已读状态
const change_chat_status = "update smart_chat set chat_receiver_reading=1 where sender_id=? and receiver_id=?";

//业主发布租赁信息
const insert_lease = "insert into smart_lease(house_id,lease_title,lease_price,lease_imageUrl,lease_introduce,lease_contacts,lease_tel,lease_keyword,lease_time,lease_status) values (?,?,?,?,?,?,?,?,?,0)";

//业主修改租赁信息
const update_lease = "update smart_lease set lease_title=?,lease_price=?,lease_imageUrl=?,lease_introduce=?,lease_contacts=?,lease_tel=?,lease_keyword=? where id=?";

//获取名下的租赁房子
const select_my_house = "select smart_lease.id,smart_building.building_name,smart_house.house_plate,smart_household_owner.owner_name,smart_lease.lease_title,smart_lease.lease_price,smart_lease.lease_imageUrl,smart_lease.lease_introduce,smart_lease.lease_contacts,smart_lease.lease_tel,smart_lease.lease_keyword,lease_time from smart_household_owner left join smart_house on smart_household_owner.house_id=smart_house.id left join smart_lease on smart_house.id=smart_lease.house_id left join smart_building on smart_house.building_id=smart_building.id where smart_household_owner.user_id = ?";

//下架房源
const offShelf = "delete from smart_lease where id = ?";

//根据房屋ID获取房间门牌号
const select_plate_houseId = "select house_plate from smart_house where id = ?";


//管理端
//获取租赁列表(默认降序)
const select_lease_admin = "select smart_lease.id,smart_household_owner.owner_name,building_name,house_plate,house_area,lease_title,lease_price,lease_imageUrl,lease_introduce,lease_contacts,lease_tel,lease_keyword,lease_time,lease_status,lease_index from smart_lease left join smart_house on smart_lease.house_id=smart_house.id left join smart_building on smart_house.building_id=smart_building.id left join smart_household_owner on smart_house.owner_id=smart_household_owner.id order by lease_time desc limit ? offset ?";
//降序
const select_lease_admin_asc = "select smart_lease.id,smart_household_owner.owner_name,building_name,house_plate,house_area,lease_title,lease_price,lease_imageUrl,lease_introduce,lease_contacts,lease_tel,lease_keyword,lease_time,lease_status,lease_index from smart_lease left join smart_house on smart_lease.house_id=smart_house.id left join smart_building on smart_house.building_id=smart_building.id left join smart_household_owner on smart_house.owner_id=smart_household_owner.id order by lease_time asc limit ? offset ?";
//模糊搜索标题
const select_lease_admin_title = "select smart_lease.id,smart_household_owner.owner_name,building_name,house_plate,house_area,lease_title,lease_price,lease_imageUrl,lease_introduce,lease_contacts,lease_tel,lease_keyword,lease_time,lease_status,lease_index from smart_lease left join smart_house on smart_lease.house_id=smart_house.id left join smart_building on smart_house.building_id=smart_building.id left join smart_household_owner on smart_house.owner_id=smart_household_owner.id where smart_lease.lease_title like concat('%', ? , '%')";
//模糊搜索介绍
const select_lease_admin_describe = "select smart_lease.id,smart_household_owner.owner_name,building_name,house_plate,house_area,lease_title,lease_price,lease_imageUrl,lease_introduce,lease_contacts,lease_tel,lease_keyword,lease_time,lease_status,lease_index from smart_lease left join smart_house on smart_lease.house_id=smart_house.id left join smart_building on smart_house.building_id=smart_building.id left join smart_household_owner on smart_house.owner_id=smart_household_owner.id where smart_lease.lease_introduce like concat('%', ? , '%')";
//状态筛选
const select_status_admin = "select smart_lease.id,smart_household_owner.owner_name,building_name,house_plate,house_area,lease_title,lease_price,lease_imageUrl,lease_introduce,lease_contacts,lease_tel,lease_keyword,lease_time,lease_status,lease_index from smart_lease left join smart_house on smart_lease.house_id=smart_house.id left join smart_building on smart_house.building_id=smart_building.id left join smart_household_owner on smart_house.owner_id=smart_household_owner.id where lease_status=? limit ? offset ?";
//修改租赁显示状态
const change_lease_admin = "update smart_lease set lease_status=? where id=?";
//判断层级重复
const select_lease_admin_index = "select count(*) as count from smart_lease where lease_index=?";
//修改显示层级
const change_lease_admin_index = "update smart_lease set lease_index=? where id=?";
//修改租赁信息
const select_lease_admin_info = "update smart_lease set lease_title=?,lease_price=?,lease_introduce=?,lease_contacts=?,lease_tel=?,lease_keyword=?,lease_index=? where id=?";
//删除租赁信息
const delete_lease = "delete from smart_lease where id = ?";

module.exports = {
    select_lease,
    select_lease_between,
    select_buildings,
    select_house,
    select_chat_all,
    insert_chat,
    isnotReading_chat,
    change_chat_status,
    insert_lease,
    update_lease,
    select_my_house,
    offShelf,
    select_plate_houseId,
    select_lease_admin,
    select_lease_admin_asc,
    select_lease_admin_title,
    select_lease_admin_describe,
    select_status_admin,
    change_lease_admin,
    select_lease_admin_index,
    change_lease_admin_index,
    select_lease_admin_info,
    delete_lease
}